/**
 * This project is a database of food additives, denoted code
 * E.
 * 
 * Copyright (C) 2010  Dmitry Zolotaryov
 *
 * This program is free software: you can redistribute it
 * and/or modify it under the terms of the GNU General Public
 * License as published by the Free Software Foundation,
 * either version 3 of the License, or (at your option) any
 * later version.
 *
 * This program is distributed in the hope that it will be
 * useful, but WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE.  See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public
 * License along with this program.  If not, see
 * http://www.gnu.org/licenses/.
 * 
 * Contact the author:
 * e-mail: zdv2010@gmail.com
 * ICQ: 476-467-654
 * Jabber: c0unt_zer0@jabber.org
 */
package zdv.foodadditives;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

/**
 * This class implements the data model and methods for working
 * with her.<br>
 * 
 * @author Dmitry Zolotaryov 
 */
public class MyDBAdapter {
	private static class myDBHelper extends SQLiteOpenHelper {
		private Context context;
		
		/**
		 * Consturctor
		 * @see android.database.sqlite.SQLiteOpenHelper#SQLiteOpenHelper(Context, String, CursorFactory, int)
		 * @param context
		 * @param name
		 * @param factory
		 * @param version
		 */
		public myDBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
			this.context = context; 
		}
		
		@Override
		public void onCreate(SQLiteDatabase arg0) {
			String[] sql = context.getString(R.string.db_create).split(";");
			arg0.beginTransaction();
			try {
				execMultipleSql(arg0, sql);
				arg0.setTransactionSuccessful();
			}
			catch (SQLException e){
				throw e;
			}
			finally {
				arg0.endTransaction();
			}
		}

		private void execMultipleSql(SQLiteDatabase arg0, String[] sql) {
			for(String s : sql){
				if(s.trim().length() > 0)
					arg0.execSQL(s);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
			String[] sql = context.getString(R.string.db_upgrade).split(";");
			arg0.beginTransaction();
			try{
				execMultipleSql(arg0, sql);
				arg0.setTransactionSuccessful();
			} catch (SQLException e){
				throw e;
			} finally {
				arg0.endTransaction();
			}
			onCreate(arg0);
		}

	}
	
	private static final String DATABASE_NAME = "FoodAdditivies.db";
	private static final String MAIN_TABLE = "mainTable";
	private static final int DATABASE_VERSION = 1;

	public static final String KEY_ID = "_id";
	
	public static final String KEY_NAME = "name";
	public static final int COLUMN_NAME = 1;

	public static final String KEY_NAME_RU = "name_ru";
	public static final int COLUMN_NAME_RU = 2;
	
	public static final String KEY_CATEGORIE = "categorie";
	public static final int COLUMN_CATEGORIE = 3;
	
	public static final String KEY_DANGER = "danger";
	public static final int COLUMN_DANGER = 4;
	
	public static final String KEY_DESCRIPTION = "description";
	public static final int COLUMN_DESCRIPTION = 5;

	public static final String KEY_DESCRIPTION_RU = "description_ru";
	public static final int COLUMN_DESCRIPTION_RU = 6;

	public static final String KEY_CODE = "code";
	public static final int COLUMN_CODE = 7;
	
	private SQLiteDatabase db;
	@SuppressWarnings("unused")
	private final Context context;
	private myDBHelper dbHelper;
	
	/**
	 * Constructor
	 * @param context
	 */
	public MyDBAdapter(Context context){
		this.context = context;
		dbHelper = new myDBHelper(context, DATABASE_NAME,
				null, DATABASE_VERSION);
	}
	
	/**
	 * Close the database
	 */
	public void close(){
		db.close();
	}
	
	/**
	 * Open the database
	 * @throws SQLiteException
	 */
	public void open() throws SQLiteException {
		try {
			db = dbHelper.getWritableDatabase();
		} catch (SQLiteException e){
			db = dbHelper.getReadableDatabase();
		}
	}
	
	/**
	 * Insert new entry into the database
	 * @param entry
	 * @return
	 */
	public long insertEntry(MyItem entry){
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, entry.getName());
		values.put(KEY_NAME_RU, entry.getName_ru());
		values.put(KEY_CATEGORIE, entry.getCategorie());
		values.put(KEY_DANGER, entry.getDanger());
		values.put(KEY_DESCRIPTION, entry.getDescription());
		values.put(KEY_DESCRIPTION_RU, entry.getDescription_ru());
		values.put(KEY_CODE, entry.getCode());
		return db.insert(MAIN_TABLE, null, values);
	}
	
	/**
	 * Remove entry
	 * @param id - index
	 * @return true if the entry is deleted, false if not
	 */
	public boolean removeEntry(long id){
		return db.delete(MAIN_TABLE, KEY_ID + "=" + id, null) > 0;
	}
	
	/**
	 * @return all entries from database
	 */
	public Cursor getAllEntries(){
		try{
			return db.query(MAIN_TABLE,
					new String[]{KEY_ID, KEY_NAME, KEY_NAME_RU,
						KEY_CATEGORIE, KEY_DANGER,
						KEY_DESCRIPTION, KEY_DESCRIPTION_RU, KEY_CODE},
					null, null, null, null, null);
		} catch(SQLException e){return null;}
	}
	
	/**
	 * Update entry
	 * @param id
	 * @param entry
	 * @return true if the entry is updated, false if not
	 */
	public boolean updateEntry(long id, MyItem entry){
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, entry.getName());
		values.put(KEY_NAME_RU, entry.getName_ru());
		values.put(KEY_CATEGORIE, entry.getCategorie());
		values.put(KEY_DANGER, entry.getDanger());
		values.put(KEY_DESCRIPTION, entry.getDescription());
		values.put(KEY_DESCRIPTION_RU, entry.getDescription_ru());
		values.put(KEY_CODE, entry.getCode());
		return db.update(MAIN_TABLE, values, KEY_ID + "=" +id, null) > 0;
	}
	
	/**
	 * 
	 * @param id - 
	 * @return 
	 * @throws SQLException
	 */
	public Cursor setCursorMyItem(String id) throws SQLException {
		Cursor r = db.query(true, MAIN_TABLE,
				new String[]{KEY_ID, KEY_NAME, KEY_NAME_RU,
					KEY_CATEGORIE, KEY_DANGER,
					KEY_DESCRIPTION, KEY_DESCRIPTION_RU, KEY_CODE},
			KEY_CODE + " like \"%" + id.trim().toLowerCase() + "%\"" +
			" or " +
			KEY_NAME + " like \"%" + id.trim().toLowerCase() + "%\"" +
			" or " +
			KEY_NAME_RU + " like \"%" + id.trim().toLowerCase() + "%\"" +
			" or " +
			KEY_DESCRIPTION + " like \"%" + id.trim().toLowerCase() + "%\"" +
			" or " +
			KEY_DESCRIPTION_RU + " like \"%" + id.trim().toLowerCase() + "%\""			
			, null, null,
			null, null, null);
		if((r.getCount() == 0) || !r.moveToFirst()){
			throw new SQLException();
		}
		return r;
	}
	
	/**
	 * 
	 * @param id - E-code
	 * @return MyItem
	 */
	public MyItem getEntry(String id){
		try {
			Cursor cursor = setCursorMyItem(id);
			String name = cursor.getString(COLUMN_NAME);
			String name_ru = cursor.getString(COLUMN_NAME_RU);
			int categorie = cursor.getInt(COLUMN_CATEGORIE);
			int danger = cursor.getInt(COLUMN_DANGER);
			String description = cursor.getString(COLUMN_DESCRIPTION);
			String description_ru = cursor.getString(COLUMN_DESCRIPTION_RU);
			String code = cursor.getString(COLUMN_CODE);
			return new MyItem(name, name_ru, categorie, danger,
					description, description_ru, code);
		} catch (SQLException e){return null;}
	}
}
